﻿using Cocon90.Lib.Dal.Error;
using Cocon90.Lib.Dal.Rule;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Cocon90.Lib.Dal.Tools
{
    /// <summary>
    /// SQL语句辅助类
    /// </summary>
    public class sqlHelper
    {

        /// <summary>
        /// 返回分布字符串 ，依次传入：源Sql语句（此Sql语句不能包含Order By），排序列名，是否升序，显示页数（从1开始），第页数据量
        /// </summary>
        public static string getPagedSql(string sourceSql, string orderColumnName, bool isAsc, int pageNumber, int pageSize)
        {
            var rownum = "rownum" + DateTime.Now.Millisecond + "" + DateTime.Now.Second + "" + DateTime.Now.Minute;
            return string.Format("select * from (select *,ROW_NUMBER() over (order by {0} " + (isAsc ? " Asc " : " Desc ") + ") " + rownum + " from ({1}) oldsqlstring" + DateTime.Now.Millisecond + "" + DateTime.Now.Second + "" + DateTime.Now.Minute + ") tab where tab." + rownum + " between ({2}-1)*{3}+1 and ({2})*{3}", orderColumnName, sourceSql, pageNumber, pageSize);
        }
        /// <summary>
        /// 返回排序字符串(对子表查询语句进行二次排序)，传入指定排序列名、是否升序
        /// </summary>
        public static string getOrderBySql(string sourceSql, string orderColumnName, bool isAsc)
        {
            return string.Format("select * from ({0}) OrderByTempTable" + DateTime.Now.Millisecond + "" + DateTime.Now.Second + "" + DateTime.Now.Minute + "  order by {1} {2}", sourceSql, orderColumnName, isAsc ? " Asc " : " Desc ");
        }

        /// <summary>
        /// 用于插入数据，自动将传和的参数按格式排列。如addSql("a",1,"2",4,"50")，奖返回" 'a',1,'2',4,'50' "。（如果值为bool，将自动给它添加引号。）
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string getAddSql(params object[] value)
        {
            StringBuilder sb = new StringBuilder();
            foreach (var item in value)
            {
                if (item == null)
                {
                    sb.Append(String.Format("{0},", "NULL"));
                }
                else if (item is int || item is float || item is double)
                {
                    sb.Append(String.Format("{0},", item));
                }
                else
                {
                    sb.Append(String.Format("'{0}',", item));
                }
            }
            return sb.ToString().TrimEnd(',');
        }
        /// <summary>
        /// 自动生成"@Column1,@Columns2,@Column3"字符串
        /// </summary>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public static string getAddSqlParam(List<string> columnName)
        {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < columnName.Count; i++)
            {
                sb.Append(String.Format("@{0},", columnName[i]));
            }
            return sb.ToString().TrimEnd(',');
        }
        /// <summary>
        /// 用于修改数据，自动将传入的参数按格式排列。（如果值为bool，将自动给它添加引号。）
        /// </summary>
        /// <param name="ColumnsAndValue"></param>
        /// <returns></returns>
        public static string getEditSql(Dictionary<string, object> ColumnsAndValue)
        {
            StringBuilder sb = new StringBuilder();
            foreach (var item in ColumnsAndValue.Keys)
            {
                if (ColumnsAndValue[item] == null)
                {
                    sb.Append(String.Format("{0}={1},", item, "NULL"));
                }
                else if (ColumnsAndValue[item] is int || ColumnsAndValue[item] is float || ColumnsAndValue[item] is double)
                {
                    sb.Append(String.Format("{0}={1},", item, ColumnsAndValue[item]));
                }
                else
                {
                    sb.Append(String.Format("{0}='{1}',", item, ColumnsAndValue[item]));
                }
            }
            return sb.ToString().TrimEnd(',');
        }
        /// <summary>
        /// 用于修改数据，自动生成Column1=@Column1,Column2=@Column2,Column3=@Column3
        /// </summary>
        /// <param name="ColumnsAndValue"></param>
        /// <returns></returns>
        public static string getEditSqlParam(List<string> columnName)
        {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < columnName.Count; i++)
            {
                sb.Append(String.Format("{0}=@{0},", columnName[i]));
            }
            return sb.ToString().TrimEnd(',');
        }
        /// <summary>
        /// 用于条件语句数据，自动将传入的参数按格式排列。（如果值为bool，将自动给它添加引号。）
        /// </summary>
        /// <param name="ColumnsAndValue"></param>
        /// <returns></returns>
        public static string getWhereSql(Dictionary<string, object> ColumnsAndValue)
        {
            StringBuilder sb = new StringBuilder();
            foreach (var item in ColumnsAndValue.Keys)
            {
                if (ColumnsAndValue[item] == null)
                {
                    sb.Append(String.Format(" {0} is null and", item));
                }
                else if (ColumnsAndValue[item] is int || ColumnsAndValue[item] is float || ColumnsAndValue[item] is double)
                {
                    sb.Append(String.Format(" {0}={1} and", item, ColumnsAndValue[item]));
                }
                else
                {
                    sb.Append(String.Format(" {0}='{1}' and", item, ColumnsAndValue[item]));
                }
            }
            return sb.ToString().TrimEnd("and".ToCharArray());
        }
        /// <summary>
        /// 用于条件语句数据，传出模糊查询的语句如： XXX like '%abc%' and YYY is null
        /// </summary>
        /// <param name="ColumnsAndValue"></param>
        /// <returns></returns>
        public static string getWhereLikeSql(Dictionary<string, object> ColumnsAndValue)
        {
            StringBuilder sb = new StringBuilder();
            foreach (var item in ColumnsAndValue.Keys)
            {
                if (ColumnsAndValue[item] == null)
                {
                    sb.Append(String.Format(" {0} is null and", item));
                }
                else
                {
                    sb.Append(String.Format(" {0} like '%{1}%' and", item, ColumnsAndValue[item]));
                }
            }
            return sb.ToString().TrimEnd("and".ToCharArray());
        }
        /// <summary>
        /// 用于条件语句数据，自动生成"Column1=@Column1 and Column2=@Column2 and Column3=@Column3"
        /// </summary>
        /// <param name="ColumnsAndValue"></param>
        /// <returns></returns>
        public static string getWhereSqlParam(List<string> columnName)
        {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < columnName.Count; i++)
            {
                sb.Append(String.Format(" {0}=@{0} and", columnName[i]));
            }
            return sb.ToString().TrimEnd("and".ToCharArray());
        }
        /// <summary>
        /// 用于条件语句数据，自动生成:
        /// "alise1.Column1=@alise1Column1 and alise1.Column2=@alise1Column2 and alise2.Column1=@alise2Column1"
        /// </summary>
        /// <param name="ColumnsAndValue"></param>
        /// <returns></returns>
        public static string getWhereSqlParam(List<AliseEntry> columnName)
        {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < columnName.Count; i++)
            {
                sb.Append(String.Format(" {0}.{1}=@{2} and", columnName[i].Alise, columnName[i].Name, columnName[i].Alise + columnName[i].Name));
            }
            return sb.ToString().TrimEnd("and".ToCharArray());
        }
        /// <summary>
        /// 用于查询数据，生成别名语句返回“col1 alise1,col2 alise2”
        /// </summary>
        /// <param name="ColumnsAsName"></param>
        /// <returns></returns>
        public static string getAliseSql(Dictionary<string, string> ColumnsAsName)
        {
            StringBuilder sb = new StringBuilder();
            foreach (var item in ColumnsAsName.Keys)
            {
                sb.Append(String.Format("{0} {1},", item, ColumnsAsName[item]));
            }
            return sb.ToString().TrimEnd(',');
        }
        /// <summary>
        /// 用于查询数据，生成别名语句返回“col1 alise1,col2 alise2”
        /// </summary>
        /// <param name="ColumnsAsName"></param>
        /// <returns></returns>
        public static string getAliseSql(List<AliseEntry> ColumnsAsName)
        {
            StringBuilder sb = new StringBuilder();
            foreach (var item in ColumnsAsName)
            {
                sb.Append(String.Format("{0} {1},", item.Name, item.Alise));
            }
            return sb.ToString().TrimEnd(',');
        }
        /// <summary>
        /// 用于查询数据，生成别名语句返回“tab1.col1 alise1,tab2.col2 alise2”
        /// </summary>
        /// <param name="ColumnsAsName"></param>
        /// <returns></returns>
        public static string getAliseSql(string[] tableNameAlise, Dictionary<string, string> ColumnsAsName)
        {
            if (tableNameAlise.Length != ColumnsAsName.Count) throw new LengthExcetpion("tableNameAlise长度和ColumnsAsName长度不相同，他俩必须一致");
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < ColumnsAsName.Keys.Count; i++)
            {
                sb.Append(String.Format("{0}.{1} {2},", tableNameAlise[i], ColumnsAsName.Keys.ElementAt(i), ColumnsAsName[ColumnsAsName.Keys.ElementAt(i)]));
            }
            return sb.ToString().TrimEnd(',');
        }
        /// <summary>
        /// 用于查询数据，生成别名语句返回“tab1.col1 alise1,tab2.col2 alise2”
        /// </summary>
        /// <param name="ColumnsAsName"></param>
        /// <returns></returns>
        public static string getAliseSql(string[] tableNameAlise, List<AliseEntry> ColumnsAsName)
        {
            if (tableNameAlise.Length != ColumnsAsName.Count) throw new LengthExcetpion("tableNameAlise长度和ColumnsAsName长度不相同，他俩必须一致");
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < ColumnsAsName.Count; i++)
            {
                sb.Append(String.Format("{0}.{1} {2},", tableNameAlise[i], ColumnsAsName[i].Name, ColumnsAsName[i].Alise));
            }
            return sb.ToString().TrimEnd(',');
        }
        /// <summary>
        /// 生成查询数据sql 如果有where则添加where子句(不包括where单词)，没有请传null。最后生成：select XXX as YYY from TABLENAME where WHERE
        /// </summary>
        /// <returns></returns>
        public static string getSelectSql(string tableName, string where, Dictionary<string, string> ColumnsAsName)
        {
            if (!String.IsNullOrEmpty(where))
            {
                return String.Format("select {0} from {1} where {2} ", getAliseSql(ColumnsAsName), tableName, where);
            }
            else return String.Format("select {0} from {1}", getAliseSql(ColumnsAsName), tableName);
        }
        /// <summary>
        /// 生成插入数据的Sql（传入表名，和列名及新值）
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string getInsertSql(string tableName, Dictionary<string, object> columnValueDics)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(String.Format("insert into {0} (", tableName));
            for (int i = 0; i < columnValueDics.Keys.Count; i++)
            {
                if (i == columnValueDics.Keys.Count - 1)
                    sb.AppendLine(string.Format(" {0}) values ", columnValueDics.Keys.ElementAt(i)));
                else
                    sb.AppendLine(string.Format(" {0},", columnValueDics.Keys.ElementAt(i)));
            }
            object[] objs = new object[columnValueDics.Keys.Count];
            for (int i = 0; i < columnValueDics.Keys.Count; i++)
            {
                objs[i] = columnValueDics[columnValueDics.Keys.ElementAt(i)];
            }
            sb.AppendLine(" ( " + getAddSql(objs) + " ) ");
            return sb.ToString();
        }
        /// <summary>
        /// 生成插入数据的Sql（传入表名，和列名） 生成：insert into TABLENAME(col1,col2,col3) values(@col1,@col2,@col3)
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string getInsertSqlParam(string tableName, List<string> columnNameList)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(String.Format("insert into {0} (", tableName));
            for (int i = 0; i < columnNameList.Count; i++)
            {
                if (i == columnNameList.Count - 1)
                    sb.AppendLine(string.Format(" {0}) values ", columnNameList.ElementAt(i)));
                else
                    sb.AppendLine(string.Format(" {0},", columnNameList.ElementAt(i)));
            }
            sb.AppendLine(" ( " + getAddSqlParam(columnNameList) + " ) ");
            return sb.ToString();
        }
        /// <summary>
        /// 生成更新数据的sql 如果有where则添加where子句(不包括where单词)，没有请传null。
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="where"></param>
        /// <param name="ColumnsAndValue"></param>
        /// <returns></returns>
        public static string getUpdateSql(string tableName, Dictionary<string, object> ColumnsAndValue, string where)
        {
            if (!String.IsNullOrEmpty(where))
            {
                return String.Format("update {0} set {1} where {2}", tableName, getEditSql(ColumnsAndValue), where);
            }
            else
            {
                return String.Format("update {0} set {1} ", tableName, getEditSql(ColumnsAndValue));
            }
        }
        /// <summary>
        /// 生成更新数据的sql 如果有where则添加where子句(不包括where单词)，没有请传null。自动生成：update TABLENAME set col1=@col1,col2=@col2 where XXX
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="where"></param>
        /// <param name="ColumnsAndValue"></param>
        /// <returns></returns>
        public static string getUpdateSqlParam(string tableName, List<string> columnName, string where)
        {
            if (!String.IsNullOrEmpty(where))
            {
                return String.Format("update {0} set {1} where {2}", tableName, getEditSqlParam(columnName), where);
            }
            else
            {
                return String.Format("update {0} set {1} ", tableName, getEditSqlParam(columnName));
            }
        }
        /// <summary>
        /// 生成删除数据sql  如果有where则添加where子句(不包括where单词)，没有请传null。
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public static string getDeleteSql(string tableName, string where)
        {
            if (!String.IsNullOrEmpty(where))
            {
                return String.Format("delete from {0} where {1} ", tableName, where);
            }
            else return String.Format("delete from {0} ", tableName);
        }
        /// <summary>
        /// 生成删除数据sql 传入条件数组， 最后生成 delete from TABLENAME where XX=XX and YY=YY
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public static string getDeleteSqlParam(string tableName, Dictionary<string, object> where, string appendCustomWhere = null)
        {
            if (where != null)
            {
                return String.Format("delete from {0} where {1} {2} ", tableName, getWhereSql(where), appendCustomWhere == null ? "" : " and " + appendCustomWhere);
            }
            else return String.Format("delete from {0} {1} ", tableName, appendCustomWhere == null ? "" : "where " + appendCustomWhere);
        }
        /// <summary>
        /// 取得获取批定表的主键的SQL语句 指定的表可以包含架构名，如：dbo.Address或guest.Address
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static string getPrimeryKey(string tableName)
        {
            if (tableName.Contains("."))
            {
                //如果此表包含架构名
                return String.Format("select primaryKey=COLUMN_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE CONSTRAINT_NAME=(select CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_NAME='{0}' and TABLE_SCHEMA='{1}')", tableName.Split('.')[1], tableName.Split('.')[0]);
            }
            else
            {   //如果不包含架构名
                return string.Format("select primaryKey=COLUMN_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE CONSTRAINT_NAME=(select CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_NAME='{0}')", tableName);
            }
        }
        /// <summary>
        /// 生成多表联合查询语句,需入多个表的列名和查询后要使用的别名，注意，Where子句不用写“where”单词
        /// 返回：select 别名1.Column1 列1,别名1.Column2 列2, 别名2.Column1 列3 from table1 别名1,table2 别名2 where 别名1.列1=别名2.列1 
        /// </summary>
        /// <returns></returns>
        public static string getJoinQuery(AliseEntry[] tableName, Dictionary<string, string>[] TableColumnWithAlise, string customWhere)
        {
            if (tableName.Length != TableColumnWithAlise.Length) { throw new LengthExcetpion("传入的tableName和TableColumnWithAlise的长度不相同，这里必须相同！"); }
            StringBuilder sb = new StringBuilder();
            StringBuilder tableNameString = new StringBuilder();
            for (int i = 0; i < tableName.Length; i++)
            {
                tableNameString.Append(String.Format(" {0} {1},", tableName[i].Name, tableName[i].Alise));
                List<string> tableNameAlise = new List<string>();
                for (int j = 0; j < TableColumnWithAlise[i].Count; j++)
                {
                    tableNameAlise.Add(tableName[i].Alise);
                }
                sb.Append(getAliseSql(tableNameAlise.ToArray(), TableColumnWithAlise[i]));
                sb.Append(',');
            }
            if (customWhere == null)
                return String.Format("select {0} from {1} ", sb.ToString().TrimEnd(','), tableNameString.ToString().TrimEnd(','));
            else
                return String.Format("select {0} from {1} where {2}", sb.ToString().TrimEnd(','), tableNameString.ToString().TrimEnd(','), customWhere);
        }
        /// <summary>
        /// 生成多表联合查询语句,需入多个表的列名和查询后要使用的别名，注意，Where子句不用写“where”单词，还可以添加自定义where子句
        /// 返回：select 别名1.Column1 列1,别名1.Column2 列2, 别名2.Column1 列3 from table1 别名1,table2 别名2 where 别名1.列1=@别名1列1 and 别名2.列1=@别名2列1  
        /// </summary>
        public static string getJoinQueryParam(AliseEntry[] tableName, Dictionary<string, string>[] TableColumnWithAlise, List<AliseEntry> paramWhere, string appendCustomWhere = null)
        {
            if (appendCustomWhere == null)
                return getJoinQuery(tableName, TableColumnWithAlise, getWhereSqlParam(paramWhere));
            else
                return getJoinQuery(tableName, TableColumnWithAlise, getWhereSqlParam(paramWhere) + " and " + appendCustomWhere);
        }
    }
}
